package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IShare;
import youleche.com.model.MRentVehicleInfo;
import youleche.com.model.MShare;

public class DShare implements IShare{

	/**
	 * 添加分享攻略
	 * @param con 数据库连接
	 * @param share 攻略信息
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int addShare(Connection con, MShare share) throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "INSERT shareFeel(id,title,keyword,content,userName,addTime,count,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, share.getId());
			pre.setObject(2, share.getTitle());
			pre.setObject(3, share.getKeyword());
			pre.setObject(4, share.getContent());
			pre.setObject(5, share.getUserName());
			pre.setObject(6, share.getAddTime());
			pre.setObject(7, share.getCount());
			pre.setObject(8, share.getA1());
			pre.setObject(9, share.getA2());
			pre.setObject(10, share.getA3());
			pre.setObject(11, share.getA4());
			pre.setObject(12, share.getA5());
			pre.setObject(13, share.getA6());
			pre.setObject(14, share.getA7());
			pre.setObject(15, share.getA8());
			pre.setObject(16, share.getA9());
			pre.setObject(17, share.getA10());
			pre.setObject(18, share.getA11());
			pre.setObject(19, share.getA12());
			pre.setObject(20, share.getA13());

			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
		
	}

	/**
	 * 获取分享信息列表
	 * @param con数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 每页数量
	 * @param orderBy 排序方式
	 * @return 返回信息列表
	 * @throws Exception 异常
	 */
	@Override
	public List<MShare> getShareList(Connection con, String startIndex,
			String endIndex, String orderBy) throws Exception {
		String sql = "SELECT " +
				"id,title,keyword,content,userName," +
				"addTime,count,A1,A2,A3," +
				"A4,A5,A6,A7,A8," +
				"A9,A10,A11,A12,A13" +
				" FROM shareFeel " + orderBy + " limit "+startIndex+", "+endIndex+";";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MShare> list = null;
			MShare share = null;
			if(rs != null){
				list = new LinkedList<MShare>();
				while(rs.next()){
					share = new MShare();
					share.setId(rs.getString(1));
					share.setTitle(rs.getString(2));
					share.setKeyword(rs.getString(3));
					share.setContent(rs.getString(4));
					share.setUserName(rs.getString(5));
					share.setAddTime(rs.getDate(6));
					share.setCount(rs.getString(7));
					share.setA1(rs.getString(8));
					share.setA2(rs.getString(9));
					share.setA3(rs.getString(10));
					share.setA4(rs.getString(11));
					share.setA5(rs.getString(12));
					share.setA6(rs.getString(13));
					share.setA7(rs.getString(14));
					share.setA8(rs.getString(15));
					share.setA9(rs.getString(16));
					share.setA10(rs.getString(17));
					share.setA11(rs.getString(18));
					share.setA12(rs.getString(19));
					share.setA13(rs.getString(20));
					
					list.add(share);
				}
			}

			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	
	/**
	 * 获取分享信息列表
	 * @param con数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 每页数量
	 * @param orderBy 排序方式
	 * @param userName  用户名
	 * @return 返回信息列表
	 * @throws Exception 异常
	 */
	@Override
	public List<MShare> getShareListByUserName(Connection con,
			String startIndex, String endIndex, String orderBy, String userName)
			throws Exception {
		String sql = "SELECT " +
		"id,title,keyword,content,userName," +
		"addTime,count,A1,A2,A3," +
		"A4,A5,A6,A7,A8," +
		"A9,A10,A11,A12,A13" +
		" FROM shareFeel where userName='" + userName + "' " + orderBy + " limit "+startIndex+", "+endIndex+";";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<MShare> list = null;
			MShare share = null;
			if(rs != null){
				list = new LinkedList<MShare>();
				while(rs.next()){
					share = new MShare();
					share.setId(rs.getString(1));
					share.setTitle(rs.getString(2));
					share.setKeyword(rs.getString(3));
					share.setContent(rs.getString(4));
					share.setUserName(rs.getString(5));
					share.setAddTime(rs.getDate(6));
					share.setCount(rs.getString(7));
					share.setA1(rs.getString(8));
					share.setA2(rs.getString(9));
					share.setA3(rs.getString(10));
					share.setA4(rs.getString(11));
					share.setA5(rs.getString(12));
					share.setA6(rs.getString(13));
					share.setA7(rs.getString(14));
					share.setA8(rs.getString(15));
					share.setA9(rs.getString(16));
					share.setA10(rs.getString(17));
					share.setA11(rs.getString(18));
					share.setA12(rs.getString(19));
					share.setA13(rs.getString(20));
					
					list.add(share);
				}
			}
		
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据用户名获取分享信息列表总条数
	 * @param con数据库连接
	 * @param userName  用户名
	 * @return 返回信息列表总条数
	 * @throws Exception 异常
	 */
	@Override
	public int getShareCountByUserName(Connection con, String userName)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(id) from shareFeel where userName='"+ userName +"'";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			int result = 0;
			if(rs != null){
				while(rs.next()){
					result = rs.getInt(1);
					break;
				}
			}
			
			return result;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 删除骑游趣事
	 * @param con 数据库连接
	 * @param id 骑游趣事ID
	 * @return 返回删除影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int deleteShareById(Connection con, String id) throws Exception {
		PreparedStatement ps = null;
		
		try{
			String sql = "delete from sharefeel where id='"+id+"'";
			ps = con.prepareStatement(sql);
			
			return  ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 根据id获取骑游攻略
	 * @param con数据库连接
	 * @param id id
	 * @return 返回骑游攻略
	 * @throws Exception 异常
	 */
	@Override
	public MShare getShareById(Connection con, String id) throws Exception {
		String sql = "SELECT " +
		"id,title,keyword,content,userName," +
		"addTime,count,A1,A2,A3," +
		"A4,A5,A6,A7,A8," +
		"A9,A10,A11,A12,A13" +
		" FROM shareFeel where id='"+id+"' limit 1;";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MShare share = null;
			if(rs != null){
				while(rs.next()){
					share = new MShare();
					share.setId(rs.getString(1));
					share.setTitle(rs.getString(2));
					share.setKeyword(rs.getString(3));
					share.setContent(rs.getString(4));
					share.setUserName(rs.getString(5));
					share.setAddTime(rs.getDate(6));
					share.setCount(rs.getString(7));
					share.setA1(rs.getString(8));
					share.setA2(rs.getString(9));
					share.setA3(rs.getString(10));
					share.setA4(rs.getString(11));
					share.setA5(rs.getString(12));
					share.setA6(rs.getString(13));
					share.setA7(rs.getString(14));
					share.setA8(rs.getString(15));
					share.setA9(rs.getString(16));
					share.setA10(rs.getString(17));
					share.setA11(rs.getString(18));
					share.setA12(rs.getString(19));
					share.setA13(rs.getString(20));
				}
			}
		
			return share;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
}
